package hit.dao.database;

import hit.core.Barcode;
import hit.core.HITBase;
import hit.core.Item;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Data Access Object that accesses {@link Item}s in the database
 */
public class DBItemDAO extends DBBase {

	
	/**
	 * @param databaseDAO a pointer to the DatabaseDAO that created this
	 * DBStorageUnitDAO
	 * */
	public DBItemDAO(DatabaseDAO databaseDAO){
		super(databaseDAO);
	}

	@Override
	protected PreparedStatement InitGetQuery() throws SQLException {
		String sql = "SELECT * FROM item";
		return databaseDAO.GetConnection().prepareStatement(sql);
	}

	@Override
	protected List<HITBase> ProcessGetRS(ResultSet rs) throws SQLException {
		List<HITBase> result = new ArrayList<HITBase>();
		
		while (rs.next()) {
			Item item = new Item();
			item.setId(rs.getInt(1));
			item.SetProductID(rs.getInt(2));
			item.SetBarcode(new Barcode(rs.getString(3)));
			item.SetEntry(rs.getDate(4));
			item.SetExit(rs.getDate(5));
			item.SetContainerID(rs.getInt(6));
			
			result.add(item);
		}
		
		return result;
	}

	@Override
	protected List<PreparedStatement> InitCreateStatement(HITBase object) throws SQLException {
		List<PreparedStatement> stmts = new ArrayList<PreparedStatement>();
		Item item = (Item) object;
		
		String sql = "INSERT INTO item (product_id, barcode, entry, exit, container_id) " +
				"VALUES (?, ?, ?, ?, ?)";
		String sql2 = "SELECT last_insert_rowid()";
		
		PreparedStatement stmt = databaseDAO.GetConnection().prepareStatement(sql);
		stmt.setInt(1, item.GetProductID());
		stmt.setString(2, item.GetBarcode().GetValue());
		stmt.setDate(3, new Date(item.GetEntry().getTime()));
		Date exit;
		if (item.GetExit() != null)
			exit = new Date(item.GetExit().getTime());
		else 
			exit = null;
		stmt.setDate(4, exit);
		stmt.setInt(5, item.GetContainerID());
		
		stmts.add(stmt);
		
		PreparedStatement stmt2 = databaseDAO.GetConnection().prepareStatement(sql2);
		stmts.add(stmt2);
		
		return stmts;
	}

	@Override
	protected List<PreparedStatement> InitRemoveStatement(HITBase object) throws SQLException {
		List<PreparedStatement> stmts = new ArrayList<PreparedStatement>();
		
		String sql = "DELETE FROM item WHERE item_id = ?";
		PreparedStatement stmt = databaseDAO.GetConnection().prepareStatement(sql);
		stmt.setInt(1, object.getId());
		
		stmts.add(stmt);
		return stmts;
	}

	@Override
	protected List<PreparedStatement> InitUpdateQuery(HITBase object)
			throws SQLException {
		List<PreparedStatement> stmts = new ArrayList<PreparedStatement>();
		String sql = "UPDATE item SET entry = ?, exit = ?, container_id = ? WHERE item_id = ?";
		Item item = (Item) object;
		
		PreparedStatement stmt = databaseDAO.GetConnection().prepareStatement(sql);
		stmt.setDate(1, new Date(item.GetEntry().getTime()));
		if (item.GetExit() != null) {
			stmt.setDate(2, new Date(item.GetExit().getTime()));
		}
		else {
			stmt.setDate(2, null);
		}
		stmt.setInt(3, item.GetContainerID());
		stmt.setInt(4, item.getId());
		
		stmts.add(stmt);
		return stmts;
	}

}
